package com.optimus.db;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Set;

import org.apache.log4j.BasicConfigurator;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;

/**
 * This class used for general purposes with the database. It contains static
 * methods to call as needed. There is a possibility to create a singleton
 * object of this class.
 * 
 * @author Optimus
 * 
 */
public final class DataBaseManager extends SimpleJdbcDaoSupport
{
	private static final Logger logger = Logger.getLogger(DataBaseManager.class);

	static
	{
		BasicConfigurator.configure();
	}

	/**
	 * This method perform an SQL command on the database.
	 * 
	 * @param sql
	 *            - the SQL command to perform
	 */
	public void sqlCommand(String sqlCommand)
	{
		logger.info("SQL Command: " + sqlCommand);
		getJdbcTemplate().execute(sqlCommand);
	}

	/**
	 * This method take all words from a given table and split them word by word
	 * in order to return a list of words to enable autocomplete search
	 * functionality.
	 * 
	 * @param autoCompleteTableName
	 *            - the table name in the database to extract words from it.
	 * @return List of separated words
	 */
	public List<String> getAutoCompleteTable(String autoCompleteTableName)
	{

		logger.info("inside method: getAutoCompleteTable() with table " + autoCompleteTableName);
		updateWords();
		List<String> queryForList = getJdbcTemplate().queryForList(
				"select word from " + autoCompleteTableName, String.class);
		List<String> splitedWords = new LinkedList<String>();
		for (String word : queryForList)
		{
			String[] temp = word.split(" ");
			for (int i = 0; i < temp.length; i++)
			{
				splitedWords.add(temp[i]);
			}
		}
		Set<String> set = new HashSet<String>(splitedWords);
		String[] exceptions = new String[] { ",", "[", "]", "?", "/", "-", ":", "", "of", "to" };
		for (int i = 0; i < exceptions.length; i++)
		{
			set.remove(exceptions[i]);
		}
		queryForList = null;
		queryForList = new LinkedList<String>();
		for (String str : set)
		{
			queryForList.add(str);
		}
		return queryForList;
	}

	public List<String> getListOfProductNames(String tableName)
	{
		logger.info("getListOfProductNames with " + tableName);
		return getJdbcTemplate()
				.queryForList("select product_name from " + tableName, String.class);
	}

	/**
	 * This method retrieves columns names of a given table by the metadata
	 * information of the database.
	 * 
	 * @param tableName
	 *            - the table name in the database
	 * @return - List of column names
	 */
	public List<String> getTableColumns(String tableName)
	{
		logger.info("getTableColumns() with tableName = " + tableName);
		List<String> tableCulloms = new LinkedList<String>();
		try
		{
			DatabaseMetaData metaData = getJdbcTemplate().getDataSource().getConnection()
					.getMetaData();
			ResultSet rs;
			rs = metaData.getColumns(null, "optmustore", tableName, null);
			while (rs.next())
			{
				tableCulloms.add(rs.getString("COLUMN_NAME"));
			}
			return tableCulloms;
		}
		catch (SQLException e)
		{
			logger.error(e.getMessage());
			return null;
		}
	}

	/**
	 * This method retrieves tables' names of a of the current database in use.
	 * 
	 * @return - List of tables' names
	 */
	public List<String> getTableNames()
	{
		logger.info("getTableNames()");
		List<String> tableNames = new LinkedList<String>();
		try
		{
			DatabaseMetaData metaData = getJdbcTemplate().getDataSource().getConnection()
					.getMetaData();
			ResultSet rs;
			rs = metaData.getTables(null, "optimustore", null, null);
			while (rs.next())
			{
				tableNames.add(rs.getString("TABLE_NAME"));
			}
			return tableNames;
		}
		catch (SQLException e)
		{
			logger.error(e.getMessage());
			return null;
		}
	}

	public List<String> parseColumnNames(List<String> cList)
	{
		List<String> temp = new LinkedList<String>();
		for (String column : cList)
		{
			column = column.replace("_", " ");
			StringBuilder result = new StringBuilder(column.length());
			String[] words = column.split("\\s");
			for (int i = 0, l = words.length; i < l; ++i)
			{
				if (i > 0)
				{
					result.append(" ");
				}
				result.append(Character.toUpperCase(words[i].charAt(0))).append(
						words[i].substring(1));
			}
			temp.add(result.toString());
		}
		return temp;
	}

	private void updateWords()
	{
		logger.info("Updating Autocomplete Table in database");
		List<String> pList = getListOfProductNames("product");
		int listSize = pList.size();
		for (int i = 0; i < listSize; i++)
		{
			sqlCommand("update autocomplete set word=\"" + pList.get(i) + "\" where id=" + i + 1);
		}
	}
}
